package com.siqi.hr.common.utils;

import com.siqi.hr.common.utils.json.JSONArray;
import com.siqi.hr.common.utils.json.JSONException;
import com.siqi.hr.common.utils.json.JSONObject;
import org.apache.commons.collections.map.LinkedMap;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

/**
 * 导出通用工具类
 * @author    xiaogq
 * @date      2017-01-05
 */
public class ExportUtils {

    /**
     * 导出excel文件时创建支持不同版本excel文件对象
     * @author       xiaogq
     * @param file   文件对象
     * @return
     */
    public static Workbook createWorkbook(File file)  {
        Workbook wb = null;
        FileInputStream fis = null;
        if(file.exists()){
            try {
                fis = new FileInputStream(file);
                if(file.getName().endsWith(".xls")){
                    wb = new HSSFWorkbook(fis);
                }else{
                    wb = new XSSFWorkbook(fis);
                }
                return wb;
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if(file.getName().endsWith(".xls")){
            wb=new HSSFWorkbook();
        }else{
            wb=new XSSFWorkbook();
        }
        return wb;
    }

    /**
     * 该方法用于导出csv文件
     * @author xiaogq 3013-07-26
     * @param  map           写入文件的数据
     * @param  path          文件路径
     * @param  fileName      文件路径
     * @param  splitValue    分割符
     * @param  encoding      编码格式
     * @return boolean
     */
    public static boolean exportCsv(Map<String,Object> map,String path,String fileName,String splitValue,String encoding){
        List<Map<String,Object>> dataList = (List<Map<String,Object>>) map.get("values");
        boolean isSucess = false;
        FileOutputStream out = null;
        OutputStreamWriter osw = null;
        BufferedWriter bw = null;
        try {
            //如果目录或文件不存在先创建该目录或文件
            FileUtils.createFile(path);
            File exfile = new File(path+fileName);
            out = new FileOutputStream(exfile);
            osw = new OutputStreamWriter(out);
            bw =new BufferedWriter(osw);
            if(dataList!=null && !dataList.isEmpty()){
                for(int i = 0; i < dataList.size(); i++){
                    Map<String,Object> dataMap =  dataList.get(i);
                    for(String key : dataMap.keySet()){
                        String dataValue = String.valueOf(dataMap.get(key)).trim();
                        if(key.endsWith("_textarea")){
                            //去除换行符
                            dataValue = StringUtil.replaceBlankMoreForExport(dataValue);
                        }
                        if(dataValue != null && !dataValue.equals("") && !dataValue.equals("null")){
                            if(dataValue.indexOf(".") != -1){
                                bw.append(dataValue.substring(0, dataValue.indexOf("."))).append(splitValue);
                            }else{
                                bw.append(dataValue).append(splitValue);
                            }
                        }else{
                            bw.append("").append(splitValue);
                        }
                    }
                    bw.newLine();//新建一行
                }
            }
            isSucess=true;
        } catch (Exception e) {
            isSucess=false;
        }finally{
            if(bw!=null){
                try {
                    bw.close();
                    bw=null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(osw!=null){
                try {
                    osw.close();
                    osw=null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(out!=null){
                try {
                    out.close();
                    out=null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return isSucess;
    }

    /**
     * 用poi实现实体导出通用方法（xiaogq）
     * @author       xiaogq
     * @param        map             导出数据集合
     * @param        path            导出到文件路径
     * @param        fileName        文件名
     * @return
     */
    public static void poiEntityExport(Map<String, Object> map,String path,String fileName){
        List<Map<String,Object>> dataList = (List<Map<String,Object>>) map.get("values");
        try {
            //如果目录或文件不存在先创建该目录或文件
            FileUtils.createFile(path);
            File exfile = new File(path+fileName);
            //创建一个工作薄
            Workbook wb = createWorkbook(exfile);
            FileOutputStream output = new FileOutputStream(exfile);  //读取的文件路径
            //创建一个sheet页
            Sheet sheet = wb.createSheet("sheet1");
            for(int i=0;i<dataList.size();i++){
                //创建行
                Row row = sheet.createRow(i);
                Map<String,Object> objes = dataList.get(i);
                if ( i > 0 ){
                    objes.remove("id");
                }
                int cols = 0;
                for(String key : objes.keySet()){
                    //创建列
                    Cell cell = row.createCell(cols);
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式
                    String type = returnFieldType(key);
                    if (type.equals("datetime") || type.equals("timestamp")) {
                        if (objes.get(key) != null && !String.valueOf(objes.get(key)).trim().equals("")) {
                            String dateTime = String.valueOf(objes.get(key));
                            if(dateTime.endsWith(".0")){
                                dateTime = dateTime.substring(0,dateTime.lastIndexOf("."));
                            }
                            cell.setCellValue(dateTime);//写入内容
                        } else {
                            cell.setCellValue("");//写入内容
                        }
                    }else if(type.equals("int")){
                        if (objes.get(key) != null && !String.valueOf(objes.get(key)).trim().equals("")) {
                            String intValue = objes.get(key).toString();
                            if(intValue.indexOf(".") != -1){
                                cell.setCellValue(intValue.substring(0, intValue.lastIndexOf(".")));//写入内容
                            }else{
                                cell.setCellValue(intValue);//写入内容
                            }
                        } else {
                            cell.setCellValue(0);//写入内容
                        }
                    } else {
                        if (objes.get(key) != null && !String.valueOf(objes.get(key)).trim().equals("")) {
                            cell.setCellValue(objes.get(key).toString());//写入内容
                        } else {
                            cell.setCellValue("");//写入内容
                        }
                    }
                    cols++;
                }
            }
            //写入数据
            wb.write(output);
            //关闭文件
            output.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 跟据字段表中的name字段解析出字段类型
     *
     * @author xiaogq
     * @return
     */
    public static String returnFieldType(String name) {
        String type = "";
        if (name != null && !"".equals(name.trim())) {
            if (name.toUpperCase().startsWith("PK")) {
                if (name.toUpperCase().startsWith("PK_T")) {
                    type = "varchar";
                }else {
                    type = "int";
                }
            }else {
                type = name.substring(name.lastIndexOf("_") + 1);
            }
        }
        return type;
    }

    /**
     * 导入时创建不同excel版本的Workbook对象
     * @author          xiaogq
     * @param inp       输入流对象
     * @return
     */
    public static Workbook createWorkbook(InputStream inp) {
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        try {
            if (POIFSFileSystem.hasPOIFSHeader(inp)) {
                return new HSSFWorkbook(inp);
            }
            if (POIXMLDocument.hasOOXMLHeader(inp)) {
                return new XSSFWorkbook(OPCPackage.open(inp));
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }

    /**
     * poi分批次读取excel数据集合
     * @author       xiaogq
     * @param        startRow  分批读取数据的开始行
     * @param        endRow    分批读取数据的结束行
     * @return
     */
    public static List<Map<String,Object>> poiGetEndtitySomeList(Sheet sheet,int startRow,int endRow){
        List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
        //非空行标识
        int notNUllRowNum = 0;
        Row row = null;
        if(endRow != 0){
            //去掉空行开始
            for(int i = startRow; i < endRow; i++){
                row=sheet.getRow(i);
                int cel = row.getPhysicalNumberOfCells();
                if(cel <= 0){
                    break; //（注如果excel文件中间出现空行则空行之后的数据不被读取）
                }
                notNUllRowNum++;   //行数加1
            }
            int cels = sheet.getRow(0).getPhysicalNumberOfCells();
            //去掉空行结束
            for(int m = 0; m < notNUllRowNum; m++){
                Map<String,Object> dataMap = new LinkedMap();
                row=sheet.getRow(m);
                for(int n = 0; n <= cels; n++){
                    if(n == 0){
                        dataMap.put(n+"", m+1);
                    }else{
                        try {
                            Cell cell = row.getCell(n-1);
                            switch(cell.getCellType()){
                                //如果单元格内容为String类型
                                case Cell.CELL_TYPE_STRING:
                                    dataMap.put(n+"", cell.getRichStringCellValue().toString().trim());
                                    break;
                                //如果单元格内容为数值类型
                                case Cell.CELL_TYPE_NUMERIC:
                                    //如果是日期格式
                                    if(DateUtil.isCellDateFormatted(cell)) {
                                        String cellVals = com.siqi.hr.system.common.utils.DateUtil.formatDate(cell.getDateCellValue(),"yyyy-MM-dd HH:mm:ss");
                                        dataMap.put(n+"", cellVals);
                                    } else { //数值格式
                                        dataMap.put(n+"", (long)cell.getNumericCellValue());
                                    }
                                    break;
                                //布尔类型
                                case Cell.CELL_TYPE_BOOLEAN:
                                    dataMap.put(n+"", cell.getBooleanCellValue());
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    dataMap.put(n+"", cell.getCellFormula());
                                    break;
                                default:
                                    dataMap.put(n+"", cell.getStringCellValue().trim());
                                    break;
                            }
                        } catch (Exception e) {
                            dataMap.put(n+"", "");
                        }
                    }
                }
                dataList.add(dataMap);
            }
        }
        return dataList;
    }

    /**
     * poi封装firegrid列头方法
     * @author       xiaogq
     * @cellLength   cellLength  列长
     * @return
     */
    public static Map<String,Object> getPoiFielGridColumn(Row row){
        Map<String,Object> columnMap = new LinkedMap();
        if(row != null){
            // Bootstrap Table列头
            StringBuilder columns = new StringBuilder();
            for(int i = 0; i<=row.getPhysicalNumberOfCells(); i++){
                if(i == 0){
                    columns.append("<th data-field='"+i+"' data-align='center'>No.</th>");
                }else{
                    //预览是隐藏引用字段的id列
                    if(row.getCell(i-1).toString().trim().endsWith("ID")){
                        columns.append("<th data-field='"+i+"' data-align='center' data-visible='false'>"+i+"</th>");
                    }else{
                        columns.append("<th data-field='"+i+"' data-align='center' data-sortable='true'>"+i+"</th>");
                    }
                }
            }
            columnMap.put("columns", columns);
        }
        return columnMap;
    }

    /**
     * poi封装需要导入的列头名称
     * @author       xiaogq
     * @return
     */
    public static List<String> poiGetImportColumnName(Row row){
        List<String> columnNames = new ArrayList<String>();
        if(row != null){
            for(int n = 0; n < row.getPhysicalNumberOfCells(); n++){
                columnNames.add(String.valueOf(row.getCell(n)).trim());
            }
        }
        return columnNames;
    }

    /**
     * poi读取excel数据通用方法
     * @author       xiaogq
     * @param        filePath      导入文件的路径
     * @return
     */
    public static Map<String,Object> poiReadEntityExcel(String filePath) {
        Map<String,Object> dataMap = new HashMap<String,Object>();
        //每次导入数标识
        int rowsNum = 0;
        try {
            //得到输入流对象（写入到FileInputStream）
            InputStream is = new FileInputStream(filePath);
            //得到工作薄对象
            Workbook wb = createWorkbook(is);
            //得到工作薄中的第一个工作表
            Sheet sheet = wb.getSheetAt(0);
            //得到文件中的总行数
            int rows = sheet.getPhysicalNumberOfRows();
            //注意这里主要是控制每次导入600条记录
            if(rows > 1001){
                rowsNum = 1002;
            }else{
                rowsNum = rows;
            }
            //封装firegrid数据
            List<Map<String,Object>> listMap = poiGetEndtitySomeList(sheet,0,rowsNum);
            //封装Bootstrap table列头
            Map<String,Object> columnMap = getPoiFielGridColumn(sheet.getRow(0));
            //封装列头数据
            List<String> importCells = poiGetImportColumnName(sheet.getRow(0));
            dataMap.put("sheetName", wb.getSheetName(0));
            dataMap.put("columnMap", columnMap);
            dataMap.put("listMap", listMap);
            dataMap.put("importCells", importCells);

            //关闭工作流
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataMap;
    }

    /**
     * 导入csv格式文件时将字符串转换成List数据
     * @author             xiaogq
     * @param strData      要导入字符串数据
     * @param splitValue   分隔符
     * @return
     */
    public static List<String> stringToList(String strData,String splitValue){
        List<String> celList = new ArrayList<String>();
        if(!strData.trim().equals("")){
            String [] strs = strData.split(splitValue);
            for(int i = 0; i < strs.length; i++){
                celList.add(strs[i]);
            }
        }
        return celList;
    }

    /**
     * 导入csv格式文件时将字符串转换成Map数据
     * @author             xiaogq
     * @param strData      要导入字符串数据
     * @param splitValue   分隔符
     * @param importCells  列头数据
     * @return
     */
    public static Map<String,Object> stringToMap(String strData,String splitValue,List<String> importCells){
        Map<String,Object> valueMap = new LinkedMap();
        if(!strData.trim().equals("")){
            String [] strs = strData.split(splitValue);
            for(int i = 0; i < importCells.size(); i++){
                try {
                    valueMap.put(String.valueOf(i+1), strs[i]);
                } catch (Exception e) {
                    valueMap.put(String.valueOf(i+1), "");
                    //e.printStackTrace();
                }
            }
        }
        return valueMap;
    }

    /**
     * 该方法用于导入csv文件
     * @author               xiaogq
     * @param  path          文件路径
     * @param  splitValue    分割符
     * @param  unicode       编码格式
     * @return
     * @throws Exception
     */
    public static Map<String,Object> importCsv(String path,String splitValue,String unicode){
        Map<String,Object> dataMap = new HashMap<String,Object>();
        List<String> dataList = new ArrayList<String>();
        List<String> importCells=new ArrayList<String>();
        List<Map<String,Object>> listMap = new ArrayList<Map<String,Object>>();
        File file = new File(path);
        BufferedReader br=null;
        try {
            br = new BufferedReader(new FileReader(file));
            String line = "";
            while ((line = br.readLine()) != null) {
                dataList.add(line);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(br!=null){
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        if(dataList.size()>0){
            int nums = 0;
            if(dataList.size() > 1001){
                nums = 1002;
            }else{
                nums = dataList.size();
            }
            for(int i = 0; i < nums; i++){
                String importCell = dataList.get(i);
                if(!importCell.trim().equals("")){
                    if(importCell.trim().indexOf(splitValue) != -1){
                        Map<String,Object> valueMap = new LinkedMap();
                        String splitStr = "\\"+splitValue;
                        if(i == 0){
                            importCells = stringToList(importCell, splitStr);
                            valueMap = stringToMap(importCell, splitStr,importCells);
                        }else{
                            valueMap = stringToMap(importCell, splitStr,importCells);
                        }
                        listMap.add(valueMap);
                    }
                }
            }
            dataMap.put("listMap", listMap);
            dataMap.put("importCells", importCells);
        }else{
            dataMap.put("listMap", listMap);
            dataMap.put("importCells", importCells);
        }
        return dataMap;
    }

    /**
     * 导入数据时该方法处理把json数据转化成Map集合
     * @author                  xiaogq
     * @date                    2017-01-10
     * @param imJsonArray       需要导入实体引用字段json数组
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public static List<Map<String,String>> jsonArrayToList(JSONArray imJsonArray) {
        List<Map<String,String>> list = new ArrayList<Map<String,String>>();
        try {
            for (int j = 0; j < imJsonArray.length(); j++) {
                Map<String,String> fieldMap = new LinkedMap();
                JSONObject jo = (JSONObject) imJsonArray.get(j);
                fieldMap.put("index",jo.getString("index"));
                fieldMap.put("fieldid",jo.getString("fieldid"));
                fieldMap.put("required",jo.getString("required"));
                fieldMap.put("vunique",jo.getString("vunique"));
                fieldMap.put("fieldName",jo.getString("fieldName"));
                fieldMap.put("inchinese",jo.getString("inchinese"));
                fieldMap.put("primaryField",jo.getString("primaryField"));
                list.add(fieldMap);
            }
            //将list集合根据索引排序方法
            Collections.sort(list, new Comparator(){
                public int compare(Object o1, Object o2) {
                    Map<String,String> map1 = (Map<String, String>) o1;
                    Map<String,String> map2 = (Map<String, String>) o2;
                    if(Integer.parseInt(map1.get("index")) > Integer.parseInt(map2.get("index"))){
                        return 1;
                    }else if(Integer.parseInt(map1.get("index")) < Integer.parseInt(map2.get("index"))){
                        return -1;
                    }else{
                        return 0;
                    }
                }
            });
        } catch (JSONException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 得到导入实体字段列头信息
     * @author             xiaogq
     * @param filedList    导入字段信息
     * @return
     */
    public static Map<String,Object> getImportTitle(List<Map<String,String>> filedList){
        Map<String,Object> map = new LinkedMap();
        for(Map<String,String> fieldMap : filedList){
            map.put(fieldMap.get("index"), fieldMap.get("inchinese"));
        }
        return map;
    }

    public static boolean fieldValueIsNull(String celValue){
        boolean bool = true;
        if ((celValue.equals("")) || (celValue == null) || (celValue.equals("null"))) {
            bool = false;
        }
        return bool;
    }

}
